package cn.com.taiji.oospub.wechat.scheduler;


import cn.com.taiji.oospub.wechat.common.helper.DataBaseInfo;
import cn.com.taiji.oospub.wechat.entity.Courses;
import cn.com.taiji.oospub.wechat.entity.CoursesImg;
import cn.com.taiji.oospub.wechat.service.ICoursesImgService;
import cn.com.taiji.oospub.wechat.service.ICoursesService;
import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;

@Component
@Slf4j
public class CourseScheduler {
    private static final Logger logger = LoggerFactory.getLogger(CourseScheduler.class);


    @Value("${kongxc_Diancan_Ip}")
    private String kongxc_Diancan_Ip;
    @Value("${kongxc_Diancan_oosPort}")
    private String kongxc_Diancan_oosPort;
    @Value("${kongxc_Diancan_oosSid}")
    private String kongxc_Diancan_oosSid;
    @Value("${kongxc_Diancan_oosUser}")
    private String kongxc_Diancan_oosUser;
    @Value("${kongxc_Diancan_oosPw}")
    private String kongxc_Diancan_oosPw;

    @Autowired
    private ICoursesImgService coursesImgService;
    @Autowired
    private ICoursesService coursesService;


    //把明天的菜更新为今天~~~~~暂时不用
//    @Scheduled(cron = "0/10 * * * * ?")
    public void updDateType() {
        // TODO Auto-generated method stub
        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        DataBaseInfo info = new DataBaseInfo(kongxc_Diancan_Ip, kongxc_Diancan_oosPort, kongxc_Diancan_oosSid, kongxc_Diancan_oosUser, kongxc_Diancan_oosPw, driver);

        String url = "jdbc:sqlserver://" + info.getDbIp() + ":" + info.getDbPort() + ";databaseName=" + info.getDbSid();
        String user = info.getDbUser(), password = info.getDbPw();
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            logger.info("驱动加载失败");
        }
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
            if (connection != null) {
                logger.info("连接成功 : " + connection);
            }
            String sql = "update Courses set dateType = ? where dateType = ? ";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "1");
            preparedStatement.setString(2, "2");
        } catch (SQLException e) {
            logger.error("Connection is invalid!");
            e.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }


    //    每天更新菜品图片
//    @PostConstruct
    @Scheduled(cron = "0 30 23 * * ?")
    public void getCoursesImg() {
        System.out.println("开始同步图片");
        //获取本周今日后所有菜品编号
        String endpoint = "oss-cn-beijing.aliyuncs.com";
        String accessKeyId = "LTAI4G6hfiVamzyUnkxmmw6p";
        String accessKeySecret = "olnhSV9098scnsmcpfMLt5rkpZB688";
        String bucketName = "opxprod";
        OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
        // 设置URL过期时间
        Date expiration = new Date(System.currentTimeMillis() + 3600 * 1000 * 24 * 360L);
        List<Courses> coursesList = coursesService.query().select("DISTINCT pos_no").ge("startTime", LocalDate.now()).list();
        log.info("需更新图片菜品数量:{}", coursesList.size());
        List<CoursesImg> coursesImgList;
        for (Courses courses : coursesList) {
            //查询图片
            log.info("更新菜品图片,posNo:{}", courses.getPosNo());
            coursesImgList = coursesImgService.query()
                    .eq("course_pos_no", courses.getPosNo())
                    .isNotNull("img_path")
                    .ne("img_path", "")
                    .and(i -> i.isNull("img_url").or().eq("img_url", "")).list();
            for (CoursesImg coursesImg : coursesImgList) {
                // 生成以GET方法访问的签名URL，访客可以直接通过浏览器访问相关内容。
                coursesImg.setImgUrl(ossClient.generatePresignedUrl(bucketName, coursesImg.getImgPath(), expiration).toString());
                coursesImg.setLastChangedTime(LocalDateTime.now());
            }
            coursesImgService.updateBatchById(coursesImgList);
        }
//         关闭OSSClient。
        ossClient.shutdown();
        System.out.println("同步结束");
    }
}
